﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class DataService
    {
        string strcon = ConfigurationManager.ConnectionStrings["forum"].ConnectionString;
        //connection objects
        SqlConnection con;

        public DataTable ExecuteReaderProceduce(string namePS)
        {
            DataTable reTable = new DataTable();
            try
            {
                OpenConnection();

                SqlCommand cmd = new SqlCommand(namePS, con);
                cmd.CommandType = CommandType.StoredProcedure;
                reTable.Load(cmd.ExecuteReader());

                CloseConnection();
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.ExecuteProceduce - Query: " + namePS + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            return reTable;
        }

        //use proceduce with parameters
        public DataTable ExecuteReaderProceduce(string namePS, ref SqlParameter[] prmt) {
            DataTable reTable = new DataTable();
            try
            {
                OpenConnection();

                SqlCommand cmd = new SqlCommand(namePS, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(prmt);
                reTable.Load(cmd.ExecuteReader());

                CloseConnection();
            }
            catch (SqlException e) {
                Console.Write("Error - Connection.ExecuteProceduce - Query: " + namePS + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            return reTable;
        }

        //excute reader, execute reader and return DataTable for gridview, etc
        public DataTable ExecuteReader(string sql, params SqlParameter[] prmt) {
            DataTable reTable = new DataTable();
            try
            {
                OpenConnection();

                
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddRange(prmt);
                reTable.Load(cmd.ExecuteReader());

                CloseConnection();
            }
            catch(SqlException e){
                Console.Write("Error - Connection.ExecuteReader - Query: " + sql + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            return reTable;
        }

        //ExecuteNonQuery, execute insert, update, delete, create, alter, drop.Not return
        public void ExecuteNonQuery(string sql, params SqlParameter[] prmt)
        {
            try
            {
                OpenConnection();

                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddRange(prmt);
                cmd.ExecuteNonQuery();

                CloseConnection();
            }
            catch(SqlException e){
                Console.Write("Error - Connection.ExecuteNonQuery - Query: " + sql + " \nException: " + e.StackTrace.ToString());
                return;
            }
        }

        //ExecuteScalar,excute count,sum, etc...Return first column at frist row
        public string ExecuteScalar(string sql, params SqlParameter[] prmt) {
            string reValue = "";
            try
            {
                OpenConnection();

                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddRange(prmt);
                reValue = cmd.ExecuteScalar().ToString();

                CloseConnection();
            }
            catch(SqlException e){
                Console.Write("Error - Connection.ExecuteScalar - Query: " + sql + " \nException: " + e.StackTrace.ToString());
            }
            return reValue;
        }

        //open connection
        void OpenConnection() {
            con = new SqlConnection(strcon);
            con.Open();
        }

        //close connection
        void CloseConnection() {
            con.Close();
        }
    }
}
